﻿using CallCenter.Data.BizUtilities.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.Base
{
    public class DataFactory : IDisposable
    {
        public string ConnectionString { get; set; }

        public DataFactory(string connectionString)
        {
            this.ConnectionString = connectionString;
        }

        #region Connection
        private SqlConnection _connection;
        public SqlConnection Connection
        {
            get
            {
                this._connection = this._connection ?? new SqlConnection(this.ConnectionString);
                return this._connection;
            }
        }



        #endregion

        #region Functions

        private void OpenConnection(SqlConnection connection)
        {
            if (connection.State != System.Data.ConnectionState.Open)
                connection.Open();
        }
        public SqlCommand GetCommand(string sql, bool openConnection = true, bool isCommandText = true)
        {
            var command = new SqlCommand(sql, this.Connection);

            if (openConnection && this.Connection.State != System.Data.ConnectionState.Open)
                this.Connection.Open();

            if (!isCommandText)
                command.CommandType = CommandType.StoredProcedure;
            return command;
        }

        public SqlDataAdapter GetAdapter(SqlCommand selectCommand)
        {
            var adapter = new SqlDataAdapter(selectCommand);

            if (selectCommand.Connection == null)
                selectCommand.Connection = this.Connection;

            this.OpenConnection(this.Connection);

            return adapter;
        }

        public DataSet ExecuteDataSet(SqlCommand cmd)
        {
            DataSet ds = new DataSet();

            using (var adapter = this.GetAdapter(cmd))
            {
                adapter.Fill(ds);
            }

            return ds;
        }
        public DataSet ExecuteDataSet(string sql)
        {
            DataSet ds = new DataSet();

            using (var command = this.GetCommand(sql))
            {
                using (var adapter = this.GetAdapter(command))
                {
                    adapter.Fill(ds);
                }
            }
            return ds;
        }

        public DataTable ExecuteTable(SqlCommand cmd)
        {
            var ds = this.ExecuteDataSet(cmd);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];

            return null;
        }

        public DataTable ExecuteTable(String sql)
        {
            var ds = this.ExecuteDataSet(sql);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];

            return null;
        }
        public List<T> ExecuteObjects<T>(SqlCommand cmd) where T : new()
        {
            var dt = this.ExecuteTable(cmd);

            return CommonUtility.ToListNew<T>(dt);
        }
        public List<T> ExecuteObjects<T>(string sql) where T : new()
        {
            var dt = this.ExecuteTable(sql);

            return CommonUtility.ToListNew<T>(dt);

        }
        public T ExecuteObject<T>(SqlCommand cmd) where T : new()
        {
            var dr = this.ExecuteRecord(cmd);
            return CommonUtility.ToSource<T>(dr);
        }
        public T ExecuteObject<T>(string sql) where T :new()
        {
            var dr = this.ExecuteRecord(sql);
            return CommonUtility.ToSource<T>(dr);
        }
        public DataRow ExecuteRecord(SqlCommand cmd)
        {
            var dt = this.ExecuteTable(cmd);
            if (dt != null && dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }

        public DataRow ExecuteRecord(string sql)
        {
            var dt = this.ExecuteTable(sql);
            if (dt != null && dt.Rows.Count > 0)
                return dt.Rows[0];
            return null;
        }

        public object ExecuteScalar(SqlCommand cmd)
        {
            var dr = this.ExecuteRecord(cmd);
            if (dr != null && dr.Table.Columns.Count > 0)
                return dr[0];
            return null;
        }

        public object ExecuteScalar(string sql)
        {
            var dr = this.ExecuteRecord(sql);
            if (dr != null && dr.Table.Columns.Count > 0)
                return dr[0];
            return null;
        }

        public int ExecuteNonQuery(string sql)
        {
            using (var cmd = this.GetCommand(sql))
            {
                return cmd.ExecuteNonQuery();
            }
        }

        public int ExecuteNonQuery(SqlCommand cmd)
        {
          return cmd.ExecuteNonQuery();
        }

        public SqlParameter AddParameter(SqlCommand cmd, string name, SqlDbType type, int size, object value)
        {
            var parameter = new SqlParameter(name, type, size);
            parameter.Value = value;
            cmd.Parameters.Add(parameter);

            return parameter;
        }
        #endregion

        public void Dispose()
        {
            try
            {
                if (this._connection != null)
                    this._connection.Dispose();
            }
            catch { }
        }
    }
}
